﻿<%@ WebHandler Language="C#" Class="RBWorkflowPerformanceDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
/**
 * Description: 热表厂流程绩效看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2025.7.29
 */

public class RBWorkflowPerformanceDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getTalentDevelopmentData":
                        r = getTalentDevelopmentData(jObject);//人才培养
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTechResearchData":
                        r = getTechResearchData(jObject);//科研攻关
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualityManagementData":
                        r = getQualityManagementData(jObject);//质量管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getProcessManagementData":
                        r = getProcessManagementData(jObject);//过程管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getGeneralManagementData":
                        r = getGeneralManagementData(jObject);//综合管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getProductionSupportData":
                        r = getProductionSupportData(jObject);//生产保障
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeliveryManagement":
                        r = getDeliveryManagement(jObject);//配送管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMainPlanManagementData":
                        r = getMainPlanManagementData(jObject);//主计划风险
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSkillTargetChart":
                        r = getSkillTargetChart(jObject);//技能矩阵柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSkillTargetChartDetails":
                        r = getSkillTargetChartDetails(jObject);//技能矩阵柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportSkillTargetChartDetails":
                        exportSkillTargetChartDetails(jObject);//技能矩阵柱状图--导出
                        break;
                    case "getTechResearchChart":
                        r = getSpeciallyItemPlanChart(jObject, "科研攻关计划");//科研攻关柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTechResearchChartDetails":
                        r = getSpeciallyItemPlanChartDetails(jObject, "科研攻关计划");//科研攻关柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportTechResearchChartDetail":
                        exportSpeciallyItemPlanChartDetails(jObject, "科研攻关计划");//科研攻关柱状图--导出
                        break;
                    case "getQualityImproveChart":
                        r = getSpeciallyItemPlanChart(jObject, "质量提升计划");//质量提升柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualityImproveChartDetails":
                        r = getSpeciallyItemPlanChartDetails(jObject, "质量提升计划");//质量提升柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportQualityImproveChartDetail":
                        exportSpeciallyItemPlanChartDetails(jObject, "质量提升计划");//质量提升柱状图--导出
                        break;
                    case "getMainScheduleRiskChart":
                        r = getSpeciallyItemPlanChart(jObject, "主计划风险");//主计划风险柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMainScheduleRiskChartDetails":
                        r = getSpeciallyItemPlanChartDetails(jObject, "主计划风险");//主计划风险柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportMainScheduleRiskChartDetail":
                        exportSpeciallyItemPlanChartDetails(jObject, "主计划风险");//主计划风险柱状图--导出
                        break;
                    case "getMeetingMinutesChart":
                        r = getSpeciallyItemPlanChart(jObject, "会议纪要");//会议纪要柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMeetingMinutesChartDetails":
                        r = getSpeciallyItemPlanChartDetails(jObject, "会议纪要");//会议纪要柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportMeetingMinutesChartDetail":
                        exportSpeciallyItemPlanChartDetails(jObject, "会议纪要");//会议纪要柱状图--导出
                        break;
                    case "getDeliveryChart":
                        r = getDeliveryChart(jObject);//配送管理柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getAllSetChart":
                        r = getAllSetChart(jObject);//齐套率柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getAllSetChartDetails":
                        r = getAllSetChartDetails(jObject);//齐套率柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportAllSetChartDetail":
                        exportAllSetChartDetail(jObject);//齐套率柱状图--导出
                        break;
                    case "getMESFeedbackChart":
                        r = getMESFeedbackChart(jObject);//快速响应柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMESFeedbackChartDetails":
                        r = getMESFeedbackChartDetails(jObject);//快速响应柱状图--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportMESFeedbackChartDetail":
                        exportMESFeedbackChartDetail(jObject);//快速响应柱状图--明细导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //人才培养
    Result getTalentDevelopmentData(JObject obj)
    {
        //string sql = @"SELECT ROUND(AVG(t.processvalue),2) / 100 avgvalue FROM rbskilltargetemp t WHERE t.isuse = 0";
        string sql = @"
SELECT ROUND(AVG(z.rate),2) / 100 avgvalue
FROM 
(
  SELECT t.customobjectitemsname,m.processvalue,t.customobjectitemsdesc targetvalue,m.qty,
         --25%~100%：实际值 / 目标值（当分母为0时，比值默认为0）
         --0%: 目标值 / 实际值（当分母为0时，比值默认为0）
         CASE WHEN t.customobjectitemsname = '0%' THEN (CASE WHEN NVL(m.qty,0) = 0 THEN 0 ELSE ROUND(NVL(t.customobjectitemsdesc,0) / NVL(m.qty,0),4) END) * 100
              ELSE (CASE WHEN NVL(t.customobjectitemsdesc,0) = 0 THEN 0 ELSE ROUND(NVL(m.qty,0) / NVL(t.customobjectitemsdesc,0),4) END) * 100
         END rate
  FROM customobjectitems t 
  INNER JOIN factory f ON f.factoryid = t.factoryid
  INNER JOIN customobject c ON c.customobjectid = t.customobjectid
  LEFT JOIN (
      SELECT t.processvalue,COUNT(1) qty 
      FROM rbskilltargetemp t
      GROUP BY t.processvalue 
      ORDER BY to_number(t.processvalue) DESC
  ) m ON m.processvalue || '%' = t.customobjectitemsname
  WHERE f.factoryname = '{0}' AND C.customobjectname = '技能矩阵目标值'
) z";
        sql = string.Format(sql, obj["factoryname"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //科研攻关
    Result getTechResearchData(JObject obj)
    {
        string sql = @"
SELECT ROUND(SUM(CASE WHEN t.schedule = '100' THEN 1 ELSE 0 END) / COUNT(1),4) ratevalue
FROM rbspeciallyitemplan t
WHERE t.itemresource = '科研攻关计划'";
        //要求完成时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate >= to_date('{0}','yyyy/mm/dd')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate <= to_date('{0}','yyyy/mm/dd')", obj["endOfSelectMonth"].ToString());
        }
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //质量管理
    Result getQualityManagementData(JObject obj)
    {
        string sql = @"
SELECT ROUND(SUM(CASE WHEN t.schedule = '100' THEN 1 ELSE 0 END) / COUNT(1),4) ratevalue
FROM rbspeciallyitemplan t
WHERE t.itemresource = '质量提升计划'";
        //要求完成时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate >= to_date('{0}','yyyy/mm/dd')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate <= to_date('{0}','yyyy/mm/dd')", obj["endOfSelectMonth"].ToString());
        }
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //过程管理
    Result getProcessManagementData(JObject obj)
    {
        string sql = @"
SELECT SUM(t.iscompworkflow) workflow,
       ROUND(SUM(t.iscompworkflow)/COUNT(1),4) * 100 workflowrate,
       SUM(t.iscompaps) aps,
       ROUND(SUM(t.iscompaps)/COUNT(1),4) * 100 apsrate,
       SUM(t.iscompmaterial) material,
       ROUND(SUM(t.iscompmaterial)/COUNT(1),4) * 100 materialrate,
       SUM(t.iscompresource) resources,
       ROUND(SUM(t.iscompresource)/COUNT(1),4) * 100 resourcesrate,
       SUM(t.iscompspotcheck) spotcheck,
       ROUND(SUM(t.iscompspotcheck)/COUNT(1),4) * 100 spotcheckrate,
       SUM(t.iscomptool) tool,
       ROUND(SUM(t.iscomptool)/COUNT(1),4) * 100 toolrate,
       ROUND(SUM(DECODE(t.quality,'是',1,0)) / COUNT(1),4) * 100 qualityRate,
       ROUND(SUM(DECODE(t.ncprogram,'是',1,0)) / COUNT(1),4) * 100 ncProgramRate,
       COUNT(1) total
FROM ({0}) t";
        sql = string.Format(sql, getAllSetChartSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //综合管理
    Result getGeneralManagementData(JObject obj)
    {
        string sql = @"
SELECT ROUND(SUM(CASE WHEN t.schedule = '100' THEN 1 ELSE 0 END) / COUNT(1),4) ratevalue
FROM rbspeciallyitemplan t
WHERE t.itemresource = '会议纪要'";
        //要求完成时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate >= to_date('{0}','yyyy/mm/dd')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate <= to_date('{0}','yyyy/mm/dd')", obj["endOfSelectMonth"].ToString());
        }
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //生产保障
    Result getProductionSupportData(JObject obj)
    {
        string sql = @"
                SELECT ROUND(SUM(DECODE(t.problem_status,'已关闭',1,0))/count(1),4) avgvalue 
                FROM ({0}) t";
        sql=string.Format(sql,getMESFeedbackChartSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //配送管理
    Result getDeliveryManagement(JObject obj)
    {
        string sql = @"SELECT ROUND(SUM(t.deliveryrate/100) / COUNT(1),4) ratevalue FROM rbproductionsupport t";
        if (obj["yearmonth"] != null && obj["yearmonth"].ToString() != "")
        {
            sql += string.Format(" WHERE t.yearmonth = '{0}'", obj["yearmonth"].ToString().Replace("-", ""));
        }
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //主计划风险
    Result getMainPlanManagementData(JObject obj)
    {
        string sql = @"
SELECT ROUND(SUM(CASE WHEN t.schedule = '100' THEN 1 ELSE 0 END) / COUNT(1),4) ratevalue
FROM rbspeciallyitemplan t
WHERE t.itemresource = '主计划风险'";
        //要求完成时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate >= to_date('{0}','yyyy/mm/dd')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate <= to_date('{0}','yyyy/mm/dd')", obj["endOfSelectMonth"].ToString());
        }
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string skillTargetChartSql(JObject obj)
    {
        string sql = @"
SELECT e.fullname,t.processvalue,r.skilltargetname,te.teamname,t.notes
FROM rbskilltargetemp t
LEFT JOIN employee e ON e.employeeid = t.employeeid
LEFT JOIN rbskilltargetmaint r ON r.id = t.rbskilltargetmaintid
LEFT JOIN team te ON te.teamid = r.teamid
WHERE 1=1 ";
        if (obj["processvalue"] != null && obj["processvalue"].ToString() != "")
        {
            sql += string.Format(" AND t.processvalue = '{0}'", obj["processvalue"].ToString());
        }
        return sql;
    }

    //技能矩阵柱状图
    Result getSkillTargetChart(JObject obj)
    {
        //string sql = string.Format(@"SELECT t.processvalue,COUNT(1) qty FROM ({0}) t GROUP BY t.processvalue order by to_number(t.processvalue) desc", skillTargetChartSql(obj));
        string sql = @"
SELECT t.customobjectitemsname,m.processvalue,NVL(t.customobjectitemsdesc,0) targetvalue,NVL(m.qty,0) qty
FROM customobjectitems t 
INNER JOIN factory f ON f.factoryid = t.factoryid
INNER JOIN customobject c ON c.customobjectid = t.customobjectid
LEFT JOIN (
    SELECT t.processvalue,COUNT(1) qty 
    FROM rbskilltargetemp t
    GROUP BY t.processvalue 
    ORDER BY to_number(t.processvalue) DESC
) m ON m.processvalue || '%' = t.customobjectitemsname
WHERE f.factoryname = '{0}' AND C.customobjectname = '技能矩阵目标值'";

        sql = string.Format(sql, obj["factoryname"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //技能矩阵柱状图--明细
    Result getSkillTargetChartDetails(JObject obj)
    {
        string sql = skillTargetChartSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //技能矩阵柱状图--导出
    void exportSkillTargetChartDetails(JObject obj)
    {
        string sql = skillTargetChartSql(obj);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataRow dr in dt.Rows)
        {
            dr["processvalue"] = dr["processvalue"].ToString() + "%";
        }
        dt.Columns["skilltargetname"].Caption = "指标名称";
        dt.Columns["teamname"].Caption = "班组";
        dt.Columns["fullname"].Caption = "员工";
        dt.Columns["processvalue"].Caption = "指标执行情况";
        dt.Columns["notes"].Caption = "备注";
        ExportHelper.ExportExcel(dt, obj["title"].ToString());
    }

    //科研攻关、质量提升、主计划风险、会议纪要 查询SQL
    string getSpeciallyItemPlanSql(JObject obj, string itemresource)
    {
        string sql = @"
SELECT t.itemresource,t.itemcontent,t.itemfinishprogress,t.needfinishdate,t.finishdate,t.responseemployee,t.responseteam,NVL(t.schedule,'0') schedule
FROM rbspeciallyitemplan t
WHERE 1=1 ";
        sql += string.Format(" AND t.itemresource = '{0}'", itemresource);
        //要求完成时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate >= to_date('{0}','yyyy/mm/dd')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.needfinishdate <= to_date('{0}','yyyy/mm/dd')", obj["endOfSelectMonth"].ToString());
        }
        if (obj["teamname"] != null && obj["teamname"].ToString() != "")
        {
            sql += string.Format(" AND t.responseteam = '{0}'", obj["teamname"].ToString());
        }
        if (obj["status"] != null && obj["status"].ToString() != "")
        {
            sql += string.Format(" AND NVL(t.schedule,'0') {0} '100'", obj["status"].ToString().Contains("未完成") ? "<>" : "=");
        }
        return sql;
    }

    //(科研攻关、质量提升、主计划风险、会议纪要)柱状图
    Result getSpeciallyItemPlanChart(JObject obj, string itemresource)
    {
        string sql = @"
SELECT t.responseteam,
       ROUND(SUM(CASE WHEN t.schedule = '100' THEN 1 ELSE 0 END) / count(1),4) * 100 finishrate
       ,ROUND(SUM(CASE WHEN t.schedule <> '100' THEN 1 ELSE 0 END) / count(1),4) * 100 unfinishrate        
FROM ({0}) t 
GROUP BY t.responseteam";
        sql = string.Format(sql, getSpeciallyItemPlanSql(obj, itemresource));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //(科研攻关、质量提升、主计划风险、会议纪要)柱状图明细
    Result getSpeciallyItemPlanChartDetails(JObject obj, string itemresource)
    {
        string sql = getSpeciallyItemPlanSql(obj, itemresource);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //(科研攻关、质量提升、主计划风险、会议纪要)柱状图明细导出
    void exportSpeciallyItemPlanChartDetails(JObject obj, string itemresource)
    {
        string sql = getSpeciallyItemPlanSql(obj, itemresource);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns.Add("schedule2");
        foreach (DataRow dr in dt.Rows)
        {
            dr["schedule2"] = dr["schedule"].ToString() + "%";
        }
        dt.Columns.Remove("schedule");
        dt.Columns["itemresource"].Caption = "项目来源";
        dt.Columns["itemcontent"].Caption = "项目内容";
        dt.Columns["itemfinishprogress"].Caption = "措施";
        dt.Columns["needfinishdate"].Caption = "要求完成时间";
        dt.Columns["finishdate"].Caption = "完成时间";
        dt.Columns["responseemployee"].Caption = "责任人";
        dt.Columns["responseteam"].Caption = "责任单位";
        dt.Columns["schedule2"].Caption = "完成进度";
        ExportHelper.ExportExcel(dt, obj["title"].ToString());
    }

    //配送管理
    Result getDeliveryChart(JObject obj)
    {
        string sql = @"SELECT t.deliveryrate,t.itemtypename
FROM rbproductionsupport t
WHERE t.yearmonth = '{0}'";
        sql = string.Format(sql, obj["yearmonth"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getAllSetChartSql(JObject obj)
    {
        string sql = @"
SELECT f.factoryname,f2.factoryname fromfactoryname,cci.containername,ws.workflowstepname,st.spectypename,
       aps.starttime,aps.endtime,
       t.iscompaps,t.iscompmaterial,t.iscompresource,t.iscompspotcheck,t.iscomptool,t.iscompworkflow,
       DECODE(t.iscompaps,1,'是','否') aps,
       DECODE(t.iscompmaterial,1,'是','否') material,
       DECODE(t.iscompresource,1,'是','否') resources,
       DECODE(t.iscompspotcheck,1,'是','否') spotcheck,
       DECODE(t.iscomptool,1,'是','否') tool,
       DECODE(t.iscompworkflow,1,'是','否') workflow,
       CASE WHEN EXISTS (
         SELECT 1 
         FROM containersubcontainer csc
         LEFT JOIN containercurrentinfo subc ON subc.containerid = csc.containerid
         WHERE csc.containerid = t.containerid AND csc.workflowstepname = ws.workflowstepname AND subc.containerstatus = 1
               AND EXISTS (SELECT 1 
                           FROM containercheckinfo2 cci 
                           WHERE cci.containerid = subc.containerid AND cci.isuse = 0 AND cci.rectifymeasures != '100%')
       ) THEN '否' ELSE '是' END quality,
       '是' ncprogram
FROM shipconcompleteset t
INNER JOIN apsplandetail aps ON t.containerid = aps.containerid AND aps.workflowstepid = t.workflowstepid AND aps.isuse = 0
LEFT JOIN factory f ON f.factoryid = aps.specfactoryid
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = t.workflowstepid
LEFT JOIN factory f2 ON f2.factoryid = cci.originalfactoryid
LEFT JOIN spec s ON s.specid = aps.specid
LEFT JOIN spectype st ON st.spectypeid = s.spectypeid
WHERE 1=1";
        if (obj["factoryid"] != null && obj["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND aps.specfactoryid = '{0}'", obj["factoryid"].ToString());
        }
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(aps.starttime) >= TO_DATE('{0}','yyyy-mm-dd') + 2", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(aps.starttime) <= TO_DATE('{0}','yyyy-mm-dd') + 2", obj["endOfSelectMonth"].ToString());
        }
        if (obj["itemname"] != null && obj["itemname"].ToString() != "")
        {
            string itemname = obj["itemname"].ToString();
            switch (itemname)
            {
                case "工艺路线齐套率":
                    sql += " AND NVL(t.iscompworkflow,0) = 0";
                    break;
                case "排产数据齐套率":
                    sql += " AND NVL(t.iscompaps,0) = 0";
                    break;
                case "工装工具齐套率":
                    sql += " AND NVL(t.iscomptool,0) = 0";
                    break;
                case "生产物资齐套率":
                    sql += " AND NVL(t.iscompmaterial,0) = 0";
                    break;
                case "设备状态齐套率":
                    sql += " AND NVL(t.iscompresource,0) = 0";
                    break;
                case "特殊过程齐套率":
                    sql += " AND NVL(t.iscompspotcheck,0) = 0";
                    break;
                case "质量预警":
                    sql = string.Format("SELECT w.* FROM ({0}) w WHERE w.quality = '否'", sql);
                    break;
                case "数控程序":
                    sql += " AND 1=1";
                    break;
                default:
                    break;
            }
        }
        return sql;
    }

    //齐套率柱状图
    Result getAllSetChart(JObject obj)
    {
        string sql = @"
SELECT SUM(t.iscompworkflow) workflow,
       ROUND(SUM(t.iscompworkflow)/COUNT(1),4) * 100 workflowrate,
       SUM(t.iscompaps) aps,
       ROUND(SUM(t.iscompaps)/COUNT(1),4) * 100 apsrate,
       SUM(t.iscompmaterial) material,
       ROUND(SUM(t.iscompmaterial)/COUNT(1),4) * 100 materialrate,
       SUM(t.iscompresource) resources,
       ROUND(SUM(t.iscompresource)/COUNT(1),4) * 100 resourcesrate,
       SUM(t.iscompspotcheck) spotcheck,
       ROUND(SUM(t.iscompspotcheck)/COUNT(1),4) * 100 spotcheckrate,
       SUM(t.iscomptool) tool,
       ROUND(SUM(t.iscomptool)/COUNT(1),4) * 100 toolrate,
       ROUND(SUM(DECODE(t.quality,'是',1,0)) / COUNT(1),4) * 100 qualityRate,
       ROUND(SUM(DECODE(t.ncprogram,'是',1,0)) / COUNT(1),4) * 100 ncProgramRate,
       COUNT(1) total
FROM ({0}) t";
        sql = string.Format(sql, getAllSetChartSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //齐套率柱状图--明细
    Result getAllSetChartDetails(JObject obj)
    {
        string sql = getAllSetChartSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportAllSetChartDetail(JObject obj)
    {
        string sql = getAllSetChartSql(obj);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns.Remove("iscompaps");
        dt.Columns.Remove("iscompmaterial");
        dt.Columns.Remove("iscompresource");
        dt.Columns.Remove("iscompspotcheck");
        dt.Columns.Remove("iscomptool");
        dt.Columns.Remove("iscompworkflow");

        dt.Columns["containername"].Caption = "批次号";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["factoryname"].Caption = "承制分厂";
        dt.Columns["fromfactoryname"].Caption = "主制分厂";
        dt.Columns["starttime"].Caption = "转工计划开始时间";
        dt.Columns["endtime"].Caption = "转工计划完成时间";
        dt.Columns["spectypename"].Caption = "工艺类型";
        dt.Columns["aps"].Caption = "排产数据齐套";
        dt.Columns["material"].Caption = "排产数据齐套";
        dt.Columns["resources"].Caption = "设备状态齐套";
        dt.Columns["spotcheck"].Caption = "特殊过程齐套";
        dt.Columns["tool"].Caption = "工装工具齐套";
        dt.Columns["workflow"].Caption = "工艺路线齐套";
        dt.Columns["quality"].Caption = "质量预警";
        dt.Columns["ncprogram"].Caption = "数控程序";
        ExportHelper.ExportExcel(dt, obj["title"].ToString());
    }

    string getMESFeedbackChartSql(JObject obj)
    {
        string sql = @"
SELECT e.fullname create_emp,up.createdate,pt.problemtype,up.problem_feedback_title,
       SUBSTR(up.lczg_emp,INSTR(up.lczg_emp, '<') + 1,INSTR(up.lczg_emp, '>') - INSTR(up.lczg_emp, '<') - 1) lczg_emp,up.lczg_receive_date,up.lczg_result,
       CASE WHEN up.lczg_receive_date IS NOT NULL and up.lczg_blbz IS NULL THEN '已响应' ELSE '未响应' END status,up.problem_status
FROM bo_eu_mes_problem_feedback up
INNER JOIN bpm_org_view og ON og.userid = up.createuser
INNER JOIN bo_eu_mes_problem_type pt ON pt.id = up.feedback_type
LEFT JOIN employee e ON 'MES_' || e.employeename = up.create_emp
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
WHERE 1=1 AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)";//NVL(up.problem_status,'未关闭') = '未关闭'";

        if (obj["factoryname"] != null && obj["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND og.factoryname = '{0}'", obj["factoryname"].ToString());
        }
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','yyyy-mm-dd')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','yyyy-mm-dd')", obj["endOfSelectMonth"].ToString());
        }
        if (obj["problemtype"] != null && obj["problemtype"].ToString() != "")
        {
            sql += string.Format(" AND pt.problemtype = '{0}'", obj["problemtype"].ToString());
        }
        if (obj["status"] != null)
        {
            string status = obj["status"].ToString();
            if (status == "已响应")
            {
                sql += " AND up.lczg_receive_date IS NOT NULL and up.lczg_blbz IS NULL";
            }
            else if (status == "未响应")
            {
                sql += " AND (and up.lczg_blbz IS NOT NULL OR up.lczg_receive_date IS NULL)";
            }
            else if (status == "已关闭")
            {
                sql += " AND NVL(up.problem_status,'未关闭') = '已关闭'";
            }
        }
        return sql;
    }

    //快速响应柱状图
    Result getMESFeedbackChart(JObject obj)
    {
        string sql = @"
SELECT t.problemtype,SUM(DECODE(t.problem_status,'已关闭',1,0)) completed_qty,SUM(DECODE(t.status,'未响应',1,0)) uncompleted_qty,count(1) total_qty
FROM ({0}) t
GROUP BY t.problemtype order by t.problemtype";
        sql = string.Format(sql, getMESFeedbackChartSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //快速响应柱状图--明细
    Result getMESFeedbackChartDetails(JObject obj)
    {
        string sql = getMESFeedbackChartSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //快速响应柱状图--明细导出
    void exportMESFeedbackChartDetail(JObject obj)
    {
        string sql = getMESFeedbackChartSql(obj);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["create_emp"].Caption = "提出人";
        dt.Columns["createdate"].Caption = "提出时间";
        dt.Columns["problemtype"].Caption = "问题类型";
        dt.Columns["problem_feedback_title"].Caption = "问题描述";
        dt.Columns["lczg_emp"].Caption = "流程主管";
        dt.Columns["lczg_receive_date"].Caption = "流程主管响应时间";
        dt.Columns["lczg_result"].Caption = "流程主管处理意见";
        dt.Columns["status"].Caption = "响应状态";
        ExportHelper.ExportExcel(dt, obj["title"].ToString());
    }
}
